package uy.edu.um.ltic.server.persistence;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;

import uy.edu.um.ltic.exception.DataBaseException;
import uy.edu.um.ltic.server.business.Conference;
import uy.edu.um.ltic.server.business.ConferenceReminderStarter;
import uy.edu.um.ltic.server.business.Employee;
import uy.edu.um.ltic.server.pool.DataBaseConnectionMgr;

public class ConferenceDAOMgr implements ConferenceDAOMgt {

	private static ConferenceDAOMgr instance = null;

	private ConferenceDAOMgr() {
	}

	public static ConferenceDAOMgr getInstance() {
		if (instance == null) {
			instance = new ConferenceDAOMgr();
		}
		return instance;
	}

	@Override
	public void addConference(Conference oConference) throws DataBaseException{
		Connection oConnection = this.openConnection();
		String executeThis = "INSERT INTO Conference (date, nOrganizer, name) VALUES (?, ?, ?)";
		String executeThisAfterwards = "INSERT INTO ConferenceParticipants VALUES (?, ?)";
		PreparedStatement oPreparedStatement;
		PreparedStatement oPreparedStatement2;
		int id = 0;
		try {
			oPreparedStatement = oConnection.prepareStatement(executeThis);

			oPreparedStatement.setTimestamp(1, oConference.getDate());
			oPreparedStatement.setInt(2, oConference.getOrganizer()
					.getnEmployee());
			oPreparedStatement.setString(3, oConference.getName());
			oPreparedStatement.execute();
			oPreparedStatement.close();

			id = getIdConference(oConnection);

			for (int i = 0; i < oConference.getParticipants().size(); i++) {
				oPreparedStatement2 = oConnection
						.prepareStatement(executeThisAfterwards);
				oPreparedStatement2.setInt(1, id);
				oPreparedStatement2.setInt(2, oConference.getParticipants()
						.get(i).getnEmployee());
				oPreparedStatement2.execute();
				
				//ConferenceReminderStarter.getInstance().scheduleReminder(id);
				
			}
			ConferenceReminderStarter.getInstance().scheduleStart(id);
		} catch (SQLException e) {
			throw new DataBaseException();
		}finally{
			try {
				oConnection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}

	private int getIdConference(Connection oConnection) throws DataBaseException{
		String getCounter = "SELECT COUNT(*) FROM Conference;";
		Statement oStatement;
		ResultSet oResult;
		int result = 1;
		try {
			oStatement = oConnection.createStatement();
			oResult = oStatement.executeQuery(getCounter);
			while (oResult.next()) {
				result = oResult.getInt(1);
			}
			oResult.close();
		} catch (SQLException e) {
			 throw new DataBaseException();
		}
		return result;
	}

	public String getConferenceName(int idConf) throws DataBaseException{
		String confName = null;
		String getCounter = "SELECT name FROM Conference WHERE idConference = ?;";
		Connection oConnection = this.openConnection();
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(getCounter);
			oPreparedStatement.setInt(1, idConf);
			oPreparedStatement.execute();
			ResultSet oResult = oPreparedStatement.executeQuery();
			while (oResult.next()) {
				confName = oResult.getString(1);
				
			}
			oResult.close();
			oPreparedStatement.close();
		} catch (SQLException e) {
			 throw new DataBaseException();
		}finally{
			try {
				oConnection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return confName;
	}
	
	@Override
	public void deleteConference(Conference c) throws DataBaseException {
		Connection oConnection = this.openConnection();
		String executeThis = "DELETE FROM ConferenceParticipants WHERE idConference = ?";
		String executeThisAfterwards = "DELETE FROM Conference WHERE idConference = ?";
		PreparedStatement oPreparedStatement;
		PreparedStatement oPreparedStatement2;
		try {
			oPreparedStatement = oConnection.prepareStatement(executeThis);

			oPreparedStatement.setInt(1,
					getIdConferenceFromConference(oConnection, c));
			oPreparedStatement.execute();
			oPreparedStatement.close();

			oPreparedStatement2 = oConnection
					.prepareStatement(executeThisAfterwards);
			oPreparedStatement2.setInt(1,
					getIdConferenceFromConference(oConnection, c));
			oPreparedStatement2.execute();
			oPreparedStatement2.close();

		} catch (SQLException e) {
			 throw new DataBaseException();
		}finally{
			try {
				oConnection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	// Se considera que un organizador no puede hacer dos conferencias con el
	// mismo nombre a la misma hora.
	private int getIdConferenceFromConference(Connection oConnection,
			Conference oConference) throws DataBaseException {
		String execute = "SELECT c.idConference FROM Conference c, ConferenceParticipants p WHERE "
				+ "c.idConference = p.idConference AND c.date = ? AND "
				+ "c.nOrganizer = ? AND c.name = ?;";
		PreparedStatement oPreparedStatement;
		int id = 1;
		try {
			oPreparedStatement = oConnection.prepareStatement(execute);

			oPreparedStatement.setTimestamp(1, oConference.getDate());
			oPreparedStatement.setInt(2, oConference.getOrganizer()
					.getnEmployee());
			oPreparedStatement.setString(3, oConference.getName());
			ResultSet oResult = oPreparedStatement.executeQuery();
			while (oResult.next()) {
				id = oResult.getInt(1);
			}
			oResult.close();
			oPreparedStatement.close();
		} catch (SQLException e) {
			 throw new DataBaseException();
		
		}
		return id;
	}

	@Override
	public void createConferenceTable() throws DataBaseException {
		Connection oConnection = this.openConnection();
		String executeThis = "CREATE TABLE Conference (" + "date TIMESTAMP, "
				+ "idConference INT NOT NULL AUTO_INCREMENT PRIMARY KEY, "
				+ "nOrganizer INT, "
				+ "name VARCHAR(80), "
				+ "FOREIGN KEY (nOrganizer) REFERENCES Employees(nEmployee) ON DELETE CASCADE ON UPDATE CASCADE);";
		String executeThisToo = "CREATE TABLE ConferenceParticipants ("
				+ "idConference INT, "
				+ "nParticipant INT,"
				+ "PRIMARY KEY(idConference, nParticipant), "
				+ "FOREIGN KEY (idConference) REFERENCES Conference(idConference) ON DELETE CASCADE ON UPDATE CASCADE, "
				+ "FOREIGN KEY (nParticipant) REFERENCES Employees(nEmployee) ON DELETE CASCADE ON UPDATE CASCADE);";
		Statement oStatement;
		try {
			oStatement = oConnection.createStatement();
			oStatement.execute(executeThis);
			oStatement.execute(executeThisToo);
		} catch (SQLException e) {
			throw new DataBaseException();
		}finally{
			try {
				oConnection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	@Override
	public Connection openConnection() throws DataBaseException {
		Connection oConnection = null;
		try {
			oConnection = DataBaseConnectionMgr.getInstance().getConnection();
		} catch (SQLException e2) {
			throw new DataBaseException();
		}
		return oConnection;
	}

	@Override
	public ArrayList<Conference> getConferencesImIn(Employee oEmployee) throws DataBaseException {
		ArrayList<Conference> result = new ArrayList<Conference>();
		ArrayList<Employee> participants = new ArrayList<Employee>();
		result = getConferencesImTheOrganizer(oEmployee);
		Connection oConnection = this.openConnection();
		String executeThis = "SELECT idConference FROM ConferenceParticipants WHERE nParticipant = ?";
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(executeThis);
			oPreparedStatement.setInt(1, oEmployee.getnEmployee());
			ResultSet oResult = oPreparedStatement.executeQuery();
			while (oResult.next()) {
				participants = getParticipants(oResult.getInt(1), oConnection);
				Conference oConference = getInfoAboutConference(oResult.getInt(1), oConnection);
				oConference.setParticipants(participants);
				result.add(oConference);
			}
			oResult.close();
			Collections.sort(result, new Comparator<Conference>() {
				public int compare(Conference s1, Conference s2) {
					return s1.getDate().compareTo(s2.getDate());
				}
			});
		} catch (SQLException e) {
			throw new DataBaseException();
		}finally{
			try {
				oConnection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return result;
	}

	private Conference getInfoAboutConference(int idConference,
			Connection oConnection) throws DataBaseException {
		Conference oConference = null;
		String executeThis = "SELECT date, nOrganizer, name FROM Conference "
				+ "WHERE idConference = ?";
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(executeThis);
			oPreparedStatement.setInt(1, idConference);
			ResultSet oResult = oPreparedStatement.executeQuery();
			while (oResult.next()) {
				int nEmployee = oResult.getInt(2);
				String execute2 = "SELECT e.name, e.surname, e.document, "
						+ "e.nEmployee, e.positionInCompany, e.currentCountry, e.branch, "
						+ "e.department, e.username, e.password, e.documentType, e.image, "
						+ "e.availability, e.email, e.administrator, e.salt FROM Employees e WHERE nEmployee = ?";
				PreparedStatement oPreparedStatement2 = oConnection
						.prepareStatement(execute2);
				oPreparedStatement2.setInt(1, nEmployee);
				ResultSet oResult2 = oPreparedStatement2.executeQuery();
				Employee oEmployee = null;
				while (oResult2.next()) {
					if (oResult2.getBlob(12) == null) {
						oEmployee = new Employee(oResult2.getString(1),
								oResult2.getString(2), oResult2.getInt(3),
								oResult2.getInt(4), oResult2.getString(5),
								oResult2.getString(6), oResult2.getString(7),
								oResult2.getString(8), oResult2.getString(9),
								oResult2.getString(10), oResult2.getString(11),
								null, oResult2.getString(13),
								oResult2.getString(14),
								oResult2.getBoolean(15), oResult2.getString(16));
					} else {
						oEmployee = new Employee(oResult2.getString(1),
								oResult2.getString(2), oResult2.getInt(3),
								oResult2.getInt(4), oResult2.getString(5),
								oResult2.getString(6), oResult2.getString(7),
								oResult2.getString(8), oResult2.getString(9),
								oResult2.getString(10), oResult2.getString(11),
								oResult2.getBlob(12).getBytes(1,
										(int) oResult2.getBlob(12).length()),
								oResult2.getString(13), oResult2.getString(14),
								oResult2.getBoolean(15), oResult2.getString(16));
					}
				}
				oResult2.close();
				oConference = new Conference(oResult.getTimestamp(1), oEmployee, oResult.getString(3));
			}
			oResult.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		
		}
		return oConference;
	}

	@Override
	public ArrayList<Conference> getConferencesImTheOrganizer(Employee oEmployee) throws DataBaseException {
		ArrayList<Conference> result = new ArrayList<Conference>();
		Connection oConnection = this.openConnection();
		String executeThis = "SELECT date, idConference, nOrganizer, name FROM Conference "
				+ "WHERE nOrganizer = ? ORDER BY date";
		PreparedStatement oPreparedStatement;
		Conference oConference = null;
		ArrayList<Integer> identifierConference = new ArrayList<Integer>();
		int identifier = 0;
		ArrayList<Employee> oParticipants = new ArrayList<Employee>();
		try {
			oPreparedStatement = oConnection.prepareStatement(executeThis);
			oPreparedStatement.setInt(1, oEmployee.getnEmployee());
			ResultSet oResult = oPreparedStatement.executeQuery();
			while (oResult.next()) {
				oConference = new Conference(oResult.getTimestamp(1),
						oEmployee, oResult.getString(4));
				identifier = oResult.getInt(2);
				result.add(oConference);
				identifierConference.add(identifier);
			}
			oResult.close();
			for (int i = 0; i < identifierConference.size(); i++) { // identifierConference.size()
																	// =
																	// result.size()
				oParticipants = getParticipants(identifierConference.get(i),
						oConnection);
				result.get(i).setParticipants(oParticipants);
			}

		} catch (SQLException e) {
			throw new DataBaseException();
		}finally{
			try {
				oConnection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return result;
	}

	private ArrayList<Employee> getParticipants(int idConference,
			Connection oConnection) throws DataBaseException {
		ArrayList<Employee> result = new ArrayList<Employee>();
		String execute = "SELECT nParticipant FROM ConferenceParticipants WHERE idConference = ?";
		PreparedStatement oPreparedStatement;
		try {
			oPreparedStatement = oConnection.prepareStatement(execute);
			oPreparedStatement.setInt(1, idConference);
			ResultSet oResult = oPreparedStatement.executeQuery();
			while (oResult.next()) {
				int nEmployee = oResult.getInt(1);

				String execute2 = "SELECT e.name, e.surname, e.document, "
						+ "e.nEmployee, e.positionInCompany, e.currentCountry, e.branch, "
						+ "e.department, e.username, e.password, e.documentType, e.image, "
						+ "e.availability, e.email, e.administrator, e.salt FROM Employees e WHERE nEmployee = ?";
				PreparedStatement oPreparedStatement2 = oConnection
						.prepareStatement(execute2);
				oPreparedStatement2.setInt(1, nEmployee);
				ResultSet oResult2 = oPreparedStatement2.executeQuery();
				Employee oEmployee = null;
				while (oResult2.next()) {
					if (oResult2.getBlob(12) == null) {
						oEmployee = new Employee(oResult2.getString(1),
								oResult2.getString(2), oResult2.getInt(3),
								oResult2.getInt(4), oResult2.getString(5),
								oResult2.getString(6), oResult2.getString(7),
								oResult2.getString(8), oResult2.getString(9),
								oResult2.getString(10), oResult2.getString(11),
								null, oResult2.getString(13),
								oResult2.getString(14),
								oResult2.getBoolean(15), oResult2.getString(16));
					} else {
						oEmployee = new Employee(oResult2.getString(1),
								oResult2.getString(2), oResult2.getInt(3),
								oResult2.getInt(4), oResult2.getString(5),
								oResult2.getString(6), oResult2.getString(7),
								oResult2.getString(8), oResult2.getString(9),
								oResult2.getString(10), oResult2.getString(11),
								oResult2.getBlob(12).getBytes(1,
										(int) oResult2.getBlob(12).length()),
								oResult2.getString(13), oResult2.getString(14),
								oResult2.getBoolean(15), oResult2.getString(16));
					}
					result.add(oEmployee);
				}
				oResult2.close();
			}
			oResult.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		
		}
		return result;
	}
	
	public ArrayList<Employee> getAllParticipants(int idConference) throws DataBaseException {
		Connection oConnection = this.openConnection();
		PreparedStatement oPreparedStatement;
		ArrayList<Employee> result = new ArrayList<Employee>();
		String executeThis = "SELECT nOrganizer FROM Conference WHERE idConference = ?";
		try {
			oPreparedStatement = oConnection.prepareStatement(executeThis);
			oPreparedStatement.setInt(1, idConference);
			ResultSet oResult = oPreparedStatement.executeQuery();
			int nEmployee = 0;
			while (oResult.next()){
				nEmployee = oResult.getInt(1);
				String execute2 = "SELECT e.name, e.surname, e.document, "
						+ "e.nEmployee, e.positionInCompany, e.currentCountry, e.branch, "
						+ "e.department, e.username, e.password, e.documentType, e.image, "
						+ "e.availability, e.email, e.administrator, e.salt FROM Employees e WHERE nEmployee = ?";
				PreparedStatement oPreparedStatement2 = oConnection
						.prepareStatement(execute2);
				oPreparedStatement2.setInt(1, nEmployee);
				ResultSet oResult2 = oPreparedStatement2.executeQuery();
				Employee oEmployee = null;
				while (oResult2.next()) {
					if (oResult2.getBlob(12) == null) {
						oEmployee = new Employee(oResult2.getString(1),
								oResult2.getString(2), oResult2.getInt(3),
								oResult2.getInt(4), oResult2.getString(5),
								oResult2.getString(6), oResult2.getString(7),
								oResult2.getString(8), oResult2.getString(9),
								oResult2.getString(10), oResult2.getString(11),
								null, oResult2.getString(13),
								oResult2.getString(14),
								oResult2.getBoolean(15), oResult2.getString(16));
					} else {
						oEmployee = new Employee(oResult2.getString(1),
								oResult2.getString(2), oResult2.getInt(3),
								oResult2.getInt(4), oResult2.getString(5),
								oResult2.getString(6), oResult2.getString(7),
								oResult2.getString(8), oResult2.getString(9),
								oResult2.getString(10), oResult2.getString(11),
								oResult2.getBlob(12).getBytes(1,
										(int) oResult2.getBlob(12).length()),
								oResult2.getString(13), oResult2.getString(14),
								oResult2.getBoolean(15), oResult2.getString(16));
					}
					result.add(oEmployee);
				}
				oResult2.close();
				ArrayList<Employee> oArray = getParticipants(idConference, oConnection);
				for (int i=0; i<oArray.size(); i++){
					result.add(oArray.get(i));
				}
			}
			oResult.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		}finally{
			try {
				oConnection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return result;
	}

	@Override
	public Timestamp getConfDate(int idConf) throws DataBaseException {
		Connection oConnection = this.openConnection();
		PreparedStatement oPreparedStatement;
		Timestamp result = new Timestamp(System.currentTimeMillis());
		String executeThis = "SELECT date FROM Conference WHERE idConference = ?";
		try {
			oPreparedStatement = oConnection.prepareStatement(executeThis);
			oPreparedStatement.setInt(1, idConf);
			ResultSet oResult = oPreparedStatement.executeQuery();
			while (oResult.next()){
				result = oResult.getTimestamp(1);
			}
			oResult.close();
		} catch (SQLException e) {
			throw new DataBaseException();
		}finally{
			try {
				oConnection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return result;
	}
	
}
